﻿using System.Collections.Generic;
using System.Linq;
using DAL.Common;
using DAL.POCO;
using Dapper;
using DapperExtensions;

namespace DAL.Repositories
{
    public class QuestionRepository
    {
        public static IEnumerable<Question> GetQuestions(int skip, int limit, out int totalItems)
        {
            string sql = @"SELECT * FROM
                              (
                                   SELECT 
                                        ROW_NUMBER() OVER (ORDER BY q.id DESC) as RowNumber, q.id, q.lvl, q.text
                                   FROM [Question] q
                              ) as x
                              WHERE RowNumber BETWEEN @startRow AND @endRow";

            string sqlCount = "SELECT COUNT(id) FROM [Question]";

            var questions = MyConnection.Current.Query<Question>(sql, new { startRow = skip, endRow = skip + limit - 1 });

            totalItems = MyConnection.Current.Query<int>(sqlCount).Single();
            return questions;
        }

        public static Question GetQuestion(int questionId)
        {
            string sql = "SELECT id, lvl, text FROM [Question] WHERE id = @id";

            var question = MyConnection.Current.Query<Question>(sql, new { id = questionId });
            return question.Single();
        }

        public static void Insert(Question question)
        {
            MyConnection.Current.Insert<Question>(question);
        }

        public static void Update(Question question)
        {
            MyConnection.Current.Update(question);
        }

        public static void Delete(int questionId)
        {
            string sql = @"DELETE FROM [Answer] WHERE questionId = @id;
                           DELETE FROM [Question] WHERE id = @id";

            MyConnection.Current.Execute(sql, new { id = questionId }); 
        }
    }
}
